package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.LeaveTeacherStudent;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 离职老师带班的学生 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-12-19
 */
public interface LeaveTeacherStudentMapper extends BaseMapper<LeaveTeacherStudent> {

    /**
     * 获取离职老师带班的学生信息
     * @param yearPart
     * @param quarterNum
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.year_part,",
            "t1.`quarter` as quarter_num ,",
            "t1.school_id,",
            "t4.schoolmaster_id,",
            "t1.teacher_id,",
            "t1.subjects_id,",
            "t1.student_id",
            "FROM",
            "record_course AS t1",
            "INNER JOIN grade AS t2 ON t1.old_grade_id = t2.id",
            "INNER JOIN staff AS t3 ON t2.teacher_id = t3.id",
            "INNER JOIN organization AS t4 ON t1.school_id = t4.id",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "AND t1.`quarter` = ${quarterNum}",
            "AND t1.lesson_type = 1",
            "AND t1.deleted = 0",
            "AND t3.`status` &lt;&gt; 1",
            "GROUP BY",
            "t1.student_id,",
            "t1.subjects_id",
            "</script>"})
    public abstract List<LeaveTeacherStudent> leaveTeacherStudent(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);

    /**
     * 获取已缴费未从主管更改为带班老师的数据
     * @param yearPart
     * @param quarterNum
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t01.id,t01.schoolmaster_id,t03.teacher_id as grade_teacher_id ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,",
            "IFNULL( sum( t2.residue_course_count ), 0 ) residue_course_count ",
            "FROM",
            "`leave_teacher_student` AS t1",
            "LEFT JOIN order_detail AS t2 ON t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t2.lesson_type = 1 ",
            "AND t1.type = 1 ",// 离职老师数据
            "GROUP BY",
            "t1.student_id ",
            ") AS t01",
            "LEFT JOIN student_grade AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN grade AS t03 ON t02.grade_id = t03.id ",
            "WHERE",
            "t01.residue_course_count &gt;= 45 ",
            "AND t02.is_last = 1 ",
            "AND t02.lesson_type = 1",
            "AND t01.schoolmaster_id &lt;&gt; t03.teacher_id",
            "</script>"})
    public abstract List<LeaveTeacherStudent> getNotAdjustedByPay(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


}
